import ConnectionDB as conn
SERVER = 1
CLIENT = 0

class CreateStructure:
	"""Contine los metodos que crean las estructuras necesarias para la sincronizacion """
	def __init__(self):
		self.directory = '../fileStructure/'
		self.synchronizedField = {'operation':'char','synchronized_version':'integer'}    #campos de control de sincronizacion
		self.postgreSQLFiles = {'create':'create_.txt','trigger':'trigger_postgreSQL.txt'}
		self.mySQLFiles = {'create':'create_txt','trigger_insert':'trigger_insert_mysql.txt','trigger_update':'trigger_update_mysql.txt','trigger_delete':'trigger_delete_mysql.txt'}
		self.create = None
		self.triggerPostgreSQL = None	
		self.triggerMySQL_I = None
		self.triggerMySQL_U = None
		self.triggerMySQL_D = None



	def init(self,tables,connection,program):
		""" Crea una estructura de sincronizacion para cada tabla """ 
		self.create = open(self.directory + self.postgreSQLFiles['create'],'r').read()
		self.triggerPostgreSQL = open(self.directory + self.postgreSQLFiles['trigger'],'r').read()
		self.triggerMySQL_I = open(self.directory + self.mySQLFiles['trigger_insert'],'r').read()
		self.triggerMySQL_U = open(self.directory + self.mySQLFiles['trigger_update'],'r').read()
		self.triggerMySQL_D = open(self.directory + self.mySQLFiles['trigger_delete'],'r').read()
		chownTable = ''
		self.program = program
		if program == SERVER:
			chownTable = 'table_server'
		else:
			chownTable = 'table_client'

		connect = connection.connect()
		cursor = connect.cursor() 
		for table in tables:
			keyField= self.__getKeyField(list(table))
			query = self.__query(table.get(chownTable),keyField)
			query_trigger = self.__create_trigger(table.get(chownTable),keyField,connection.dbms)
			cursor.execute(query)
			connect.commit()
			map(lambda x:cursor.execute(x),query_trigger)
			connect.commit()
		cursor.close()

	def __getKeyField(self, fields):
		"""Busca los campos clave """
		keyFields = []	
		for field in fields:
			if field.get("key") == "True":
				keyFields.append(field)
		return keyFields



	def __query(self,tableName,keyField):
		"""	Construye String para crear la tabla de sincronizacion	 """
		keyFields = ""
		queryField = ""
		primaryKey = ""
		fieldControlSincronize = ""
		typeField = ''
		if self.program == SERVER:
			typeField = 'fieldServer'
		else:
			typeField = 'fieldClient'
		
		for field in keyField:
			fieldTable =  field.find(typeField)
			keyFields += fieldTable.get("name") + " " + fieldTable.get("type") + ",\n "
			primaryKey += fieldTable.get("name") + ","
		
		for field, type_ in self.synchronizedField.items():
			fieldControlSincronize += field + " " + type_ + ",\n "

		primaryKey =primaryKey[0:len(primaryKey)-1] 				#clave de la tabla de sincronizacion
		queryField = self.create.format(tableName,keyFields,primaryKey,fieldControlSincronize)	
		print "Tabla creada"
		return queryField


	def __create_trigger(self,tableName, keyField, dbms):
		if dbms == "MySQL":
			trigger = self.__create_trigger_MySQL(tableName,keyField)
		elif dbms =="PostgreSQL":
			trigger = self.__create_trigger_PostgreSQL(tableName,keyField)
		elif dbms =="Oracle": 
			trigger = self.__create_trigger_ToOracle(tablename,keyField)
		return trigger



	def __create_trigger_PostgreSQL(self, tableName, keyField):
		keyField_1 = ""
		keyField_2 = ""
		keyField_3 = ""
		keyField_4 = ""
		keyField_5 = ""
		typeField = ''
		if self.program == SERVER:
			typeField = 'fieldServer'
		else:
			typeField = 'fieldClient'

		for field in keyField:
			fieldClient = field.find(typeField)
			name = fieldClient.get("name") 
			keyField_1 += name + " = " + "OLD." + name + " and "
			keyField_2 += "OLD." + name + " , "
			keyField_3 += name + " = " + "NEW." + name + " and "
			keyField_4 += "NEW." + name + " , "
			keyField_5 += name + " = " + "aux_row." + name +  " and "
			 
		keyField_1 = keyField_1[0:len(keyField_1)-5]
		keyField_2 = keyField_2[0:len(keyField_2)-3]
		keyField_3 = keyField_3[0:len(keyField_3)-5]
		keyField_4 = keyField_4[0:len(keyField_4)-3]
		keyField_5 = keyField_5[0:len(keyField_5)-5]
		queryField = self.triggerPostgreSQL.format(tableName,keyField_1,keyField_2,keyField_3,keyField_4,keyField_5)
		print "trigger creado"
		return [queryField]

	def __create_trigger_MySQL(self, tableName, keyField):
		keyField_1 = ""
		keyField_2 = ""
		keyField_3 = ""
		keyField_4 = ""
		typeField = ''
		if self.program == SERVER:
			typeField = 'fieldServer'
		else:
			typeField = 'fieldClient'
		for field in keyField:
			fieldClient = field.find(typeField)
			name = fieldClient.get("name") 
			keyField_1 += name + " = " + "NEW." + name + " and "
			keyField_2 += "NEW." + name + " , "
			keyField_3 += name + " = " + "OLD." + name + " and "
			keyField_4 += "OLD." + name + " , "

		keyField_1 = keyField_1[0:len(keyField_1)-5]
		keyField_2 = keyField_2[0:len(keyField_2)-3]
		keyField_3 = keyField_3[0:len(keyField_3)-5]
		keyField_4 = keyField_4[0:len(keyField_4)-3]
		insert = self.triggerMySQL_I.format(tableName,keyField_1,keyField_2) 
		update = self.triggerMySQL_U.format(tableName,keyField_1,keyField_2)
		delete = self.triggerMySQL_D.format(tableName,keyField_3,keyField_4)
		queryField = [insert,update, delete]
		print "trigger creado"
		return queryField
